package com.sysmanage.etl.utils;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

import com.sysmanage.etl.bo.SqlBO;
import com.sysmanage.etl.model.ETL_DataSource;

public class JdbcUtils {
	private static final String URL = "jdbc:sqlserver://172.17.88.14:1433;DatabaseName=EDW";
	private static final String USERNAME = "autocrm";
	private static final String PASSWORD = "autocrm123";

	public static Connection getConnection() throws Exception {
		Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
		return DriverManager.getConnection(URL, USERNAME, PASSWORD);
	}

	public static Connection getConnection(ETL_DataSource ds, Boolean autoCommit) throws Exception {
		if (ds == null) {
			throw new Exception("数据源不能为空");
		}
		Connection conn = null;
		String url = "";
		switch (ds.getDataSource_dialect()) {
		case Constants.MSSQLSERVER:
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
			url = "jdbc:sqlserver://" + ds.getDataSource_ip() + ":" + ds.getDataSource_port() + ";DatabaseName=" + ds.getDataSource_dbname();
			break;
		case Constants.MYSQL:
			Class.forName("com.mysql.jdbc.Driver");
			url = "jdbc:mysql://" + ds.getDataSource_ip() + ":" + ds.getDataSource_port() + "/" + ds.getDataSource_dbname() + "?useUnicode=true&characterEncoding=UTF-8";
			break;
		case Constants.ORACLE:
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
			url = "jdbc:mysql://" + ds.getDataSource_ip() + ":" + ds.getDataSource_port() + "/" + ds.getDataSource_dbname() + "?useUnicode=true&characterEncoding=UTF-8";
		}
		try {
			conn = DriverManager.getConnection(url, ds.getDataSource_username(), ds.getDataSource_password());
			conn.setAutoCommit(autoCommit);
		} catch (Exception e) {
			e.printStackTrace();
			throw new ETLConnectionException(e.getMessage());
		}
		return conn;
	}

	public static void close(Connection conn) {
		try {
			if (conn == null)
				return;
			if (!conn.isClosed())
				conn.close();
		} catch (Exception ee) {
			ee.printStackTrace();
		}
	}

	public static void commit(Connection conn) {
		try {
			if (conn == null)
				return;
			if (conn.isClosed())
				return;
			if (!(conn.isClosed() || conn.getAutoCommit()))
				conn.commit();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public static void rollback(Connection conn) {
		try {
			if (conn == null)
				return;
			if (conn.isClosed())
				return;
			if (!(conn.isClosed() || conn.getAutoCommit()))
				conn.rollback();
			;
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public static Boolean check(ETL_DataSource ds) {
		Connection conn = null;
		try {
			conn = getConnection(ds, true);
			return conn == null ? false : true;
		} catch (Exception e) {
			return false;
		} finally {
			close(conn);
		}
	}

	public static SqlBO excute(SqlBO bo, Connection conn) throws Exception {
		if (bo == null) {
			throw new Exception("SqlBO is empty!");
		}
		if (bo.getExcuted()) {
			throw new Exception("SqlBO allready excuted!");
		}
		PreparedStatement psmt = null;
		if (bo.getSql().toUpperCase().startsWith("SELECT")) {
			psmt = conn.prepareStatement(bo.getSql());
			ResultSet res = psmt.executeQuery();
			if (res.next())
				bo.setValue(String.valueOf(res.getObject(1)));
		} else if (bo.getSql().toUpperCase().startsWith("INSERT")) {
			psmt = conn.prepareStatement(bo.getSql(), Statement.RETURN_GENERATED_KEYS);
			psmt.executeUpdate();
			ResultSet res = psmt.getGeneratedKeys();
			if (res.next())
				bo.setValue(String.valueOf(res.getObject(1)));
		} else if (bo.getSql().toUpperCase().startsWith("UPDATE")) {
			psmt = conn.prepareStatement(bo.getSql(), Statement.RETURN_GENERATED_KEYS);
			psmt.executeUpdate();
			ResultSet res = psmt.getGeneratedKeys();
			if (res.next())
				bo.setValue(String.valueOf(res.getObject(1)));
		}
		bo.setExcuted(true);
		return bo;
	}

	public static Boolean queryIsEmpty(String sql, Connection conn) throws Exception {
		if (sql == null || sql.length() <= 0) {
			throw new Exception("SqlBO is empty!");
		}
		PreparedStatement psmt = conn.prepareStatement(sql);
		ResultSet res = psmt.executeQuery();
		if (res.next())
			return false;
		return true;
	}
}
